﻿/* 
 * Since 2008 - 2012
 */

package com.hh.bss.project.dao.impl;

import java.util.*;

import com.hh.bss.auth.util.AuthSqlUtil;
import com.hh.bss.base.*;
import com.hh.bss.common.ConstantDic;
import com.hh.bss.common.MapObjectRowMapper;
import com.hh.bss.common.cache.SystemUserCache;
import com.hh.bss.common.page.*;
import com.hh.bss.common.page.impl.*;
import com.hh.bss.project.dao.*;
import com.hh.bss.project.entity.*;
import com.hh.bss.project.service.*;
import com.hh.bss.project.vo.query.*;
import com.hh.bss.util.*;



import static com.hh.bss.util.ObjectUtils.*;

import org.springframework.stereotype.Repository;

@Repository
public class ContractJobCvOfferDaoImpl extends BaseHibernateDao<ContractJobCvOffer, java.lang.Integer> implements ContractJobCvOfferDao<ContractJobCvOffer, java.lang.Integer> {

	public Class getEntityClass() {
		return ContractJobCvOffer.class;
	}

	public Page findJobCvOfferPage(ContractJobCvOfferQuery query) {
		// 人选姓名，职位，客户职位联系人，上班日期，项目负责人，信息录入人，手机，电邮，客户公司 修改人 修改时间
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from ( ");
		sql.append(" select a.id,a.recom_id recomId,a.cv_id cvId,a.job_id jobId,h.jobManager,a.status cvStatus,a.expired_d expiredD,a.apply_d applyD,a.salary,b.cv_name cvName,b.company cvCompany,b.title cvTitle,a.name jobName,d.cv_name jobContacter,a.onduty_d ondutyDt,a.checkin_d checkinD,e.account owner,b1.name custName,a.creator,b.mobile,b.tel phone,b.email,a.create_dt createDt,a.modifier,a.modifiedDt  from (select distinct a1.id,a1.recom_id,a1.creator,a1.create_dt,a1.onduty_d,a1.checkin_d,a1.salary,a0.status,a1.expired_d,a1.apply_d,a1.modifier,a1.modified_dt modifiedDt,a0.cv_id,a0.job_id,a2.name,a2.hr_contacter,a2.ar_contacter,a2.cust_id from contract_job_cv_recom a0,contract_job_cv_offer a1,contract_job a2 where a0.id=a1.recom_id and a0.job_id=a2.id) a ");
		sql.append(" left join  cv b on a.cv_id=b.id and b.is_delete=0 ");
		// customer_contacter
		sql.append(" left join  (select t2.id,t1.name,t1.full_name from company t1,customer t2 where t1.id=t2.company_id ) b1 on a.cust_id=b1.id ");
		sql.append(" left join  (select a0.id,a0.cv_name,a1.role,a1.cust_id from cv a0,customer_contacter a1 where a0.id=a1.cv_id ) d on a.cust_id=d.cust_id and d.role='").append(ConstantDic.CUSTOMER_CONTACTER_TYPE_JOB_CONTACTER).append("' ");
		sql.append(" left join  ( select a.job_id,group_concat((b.name) separator ',') as jobManager from contract_job_partner a,system_user b where a.account=b.account and a.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' group by a.job_id) h on h.job_id=a.job_id");
		sql.append(" left join (select  t1.job_id,min(t2.account) account from   contract_job_search_project t1,search_project t2 where t1.project_id=t2.id group by t1.job_id) e on a.job_id=e.job_id ");
		// sql.append(" left join (select cv_id,group_concat((nbr) separator ',') as mobile from cv_contact_detail where contact_type='").append(ConstantDic.ContactType_MOBILE).append("' group by cv_id) f on a.cv_id=f.cv_id ");
		// sql.append(" left join (select cv_id,group_concat((concat(if(nbr_ext1 is null,'','('),ifnull(nbr_ext1,''),if(nbr_ext1 is null,'',')'),ifnull(nbr,''),if(nbr_ext2 is null,'','-'),ifnull(nbr_ext2,''))) separator ',') as phone from cv_contact_detail where contact_type='").append(ConstantDic.ContactType_TEL).append("' group by cv_id) h on a.cv_id=h.cv_id ");
		// sql.append(" left join (select cv_id,group_concat((nbr) separator ',') as email from cv_contact_detail where contact_type='").append(ConstantDic.ContactType_EMAIL).append("' group by cv_id) i on a.cv_id=i.cv_id ");
		// sql.append(" left join (select cv_id,group_concat((tag) separator ',') as tag from cv_tag group by cv_id) j on a.cv_id=j.cv_id ");
		sql.append(" ) comcv ");
		List values = new ArrayList();

		sql.append(" where comcv.jobId=? ");
		values.add(query.getJobId());

		if (isNotEmpty(query.getCvId())) {
			sql.append(" and comcv.cvId = ? ");
			values.add(query.getCvId());
		}

		// return super.getJdbcDao().createPage(query, values, mapper,
		// pageMaxSize, pageNumber)
		return super.getJdbcDao().createPage(sql.toString(), values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}
	public List findJobCvOffer(ContractJobCvOfferQuery query) {
		// 人选姓名，职位，客户职位联系人，上班日期，项目负责人，信息录入人，手机，电邮，客户公司 修改人 修改时间
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from ( ");
		sql.append(" select a.id,a.recom_id recomId,a.cv_id cvId,a.job_id jobId,b.cv_name cvName,a.name jobName from (select distinct a1.id,a1.recom_id,a0.cv_id,a0.job_id,a2.name from contract_job_cv_recom a0,contract_job_cv_offer a1,contract_job a2 where a0.id=a1.recom_id and a0.job_id=a2.id) a ");
		sql.append(" left join  cv b on a.cv_id=b.id and b.is_delete=0 ");
		
		sql.append(" ) comcv ");
		List values = new ArrayList();

		sql.append(" where comcv.jobId=? ");
		values.add(query.getJobId());

		if (isNotEmpty(query.getCvId())) {
			sql.append(" and comcv.cvId = ? ");
			values.add(query.getCvId());
		}

		// return super.getJdbcDao().createPage(query, values, mapper,
		// pageMaxSize, pageNumber)
		return super.getJdbcDao().findByQuery(sql.toString(), values.toArray(), new MapObjectRowMapper());
		
	}

	public Page findPage(ContractJobCvOfferQuery query) {
		// XsqlBuilder syntax,please see
		// http://code.google.com/p/rapid-xsqlbuilder
		// [column]为字符串拼接, {column}为使用占位符.
		// [column]为使用字符串拼接,如username='[username]',偷懒时可以使用字符串拼接
		// [column] 为PageRequest的属性

		Class[] os = { ContractJobCvOfferQuery.class };
		Class property = org.springframework.beans.BeanUtils.findPropertyType(query.getField_type(), os);
		if (property != null) {
			if ("java.lang.String".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), query.getQuery());

			} else if ("java.lang.Integer".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseInteger(query.getQuery(), 0));

			} else if ("java.lang.Double".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseDouble(query.getQuery(), 0));
			} else if ("java.util.Date".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), DateHelper.toDate(query.getQuery(), DateHelper.DEFAULT_DATE_FORMAT));
			}
		}
		// 生成sql2的原因是为了不喜欢使用xsqlbuilder的同学，请修改生成器模板，删除本段的生成
		StringBuffer sql2 = new StringBuffer();
		List values = new ArrayList();
		sql2.append("select * from ( ");
		// sql2.append(" select  a.id,a.recom_id recomId,b.cv_id cvId,c.cv_name cvName,b.job_id jobId,a.apply_d applyD,a.onduty_d ondutyD,a.trial,a.expired_d expiredD,a.salary,a.currency,a.estimate_ar estimateAr,a.checkin_d checkinD,a.status,a.status_desc statusDesc,a.adjust_ar adjustAr,a.adjust_sign adjustSign,a.adjust_desc adjustDesc,a.final_ar finalAr,a.pn_status pnStatus,a.creator,a.create_dt createDt,a.modifier,a.modified_dt modifiedDt from contract_job_cv_offer a,contract_job_cv_recom b,cv c where a.recom_id=b.id and b.cv_id=c.id ");
		sql2.append(" select  a.id,a.recom_id recomId,b.cv_id cvId,c.cv_name cvName,b.job_id jobId,a.apply_d applyD,a.onduty_d ondutyD,a.trial,a.expired_d expiredD,a.salary,a.currency,a.estimate_ar estimateAr,a.checkin_d checkinD,a.status,a.status_desc statusDesc,a.adjust_ar adjustAr,a.adjust_sign adjustSign,a.adjust_desc adjustDesc,d.finalAr,a.pn_status pnStatus,a.creator,a.create_dt createDt,a.modifier,a.modified_dt modifiedDt from contract_job_cv_offer a ");
		sql2.append(" inner join contract_job_cv_recom b on a.recom_id=b.id ");
		sql2.append(" inner join cv c on b.cv_id=c.id ");
		sql2.append(" left join (select b.offer_id,sum(ifnull(a.ar_amount,0)) finalAr from account_ar a,account_pn b where a.ar_dt is not null and a.pn_nbr=b.nbr group by b.offer_id) d on d.offer_id=a.id ");
		sql2.append(" ) t where 1=1 ");

		if (isNotEmpty(query.getId())) {
			sql2.append(" and  t.id = ? ");
			values.add(query.getId());
		}
		if (isNotEmpty(query.getJobId())) {
			sql2.append(" and  t.jobId = ? ");
			values.add(query.getJobId());
		}
		if (isNotEmpty(query.getCvId())) {
			sql2.append(" and  t.cvId = ? ");
			values.add(query.getCvId());
		}

		if (query.getOnlineUser() != null) {
			String authsql = AuthSqlUtil.authJobSecSQL(query.getOnlineUser().getUsername(), "t.jobId", "t.creator");
			if (!StringHelper.isEmpty(authsql))
				sql2.append(authsql);
		}
		sql2.append(" order by t.id desc ");

		return super.getJdbcDao().createPage(sql2.toString(), values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}

	public Page findCvOfferPage(ContractJobCvOfferQuery query) {
		// TODO Auto-generated method stub
		// 职位，人选，录用时间(apply_d)，上班时间(a.onduty_d)，录用结果，预计收费estimate_ar，实际收费final_ar，保证期到期日？
		// 录用公司 客户职位联系人，联系人电话，联系人电邮
		// XsqlBuilder syntax,please see
		// http://code.google.com/p/rapid-xsqlbuilder
		// [column]为字符串拼接, {column}为使用占位符.
		// [column]为使用字符串拼接,如username='[username]',偷懒时可以使用字符串拼接
		// [column] 为PageRequest的属性

		Class[] os = { ContractJobCvOfferQuery.class };
		Class property = org.springframework.beans.BeanUtils.findPropertyType(query.getField_type(), os);
		if (property != null) {
			if ("java.lang.String".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), query.getQuery());

			} else if ("java.lang.Integer".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseInteger(query.getQuery(), 0));

			} else if ("java.lang.Double".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseDouble(query.getQuery(), 0));
			} else if ("java.util.Date".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), DateHelper.toDate(query.getQuery(), DateHelper.DEFAULT_DATE_FORMAT));
			}
		}
		// 生成sql2的原因是为了不喜欢使用xsqlbuilder的同学，请修改生成器模板，删除本段的生成
		StringBuffer sql2 = new StringBuffer();
		List values = new ArrayList();
		sql2.append("select * from ( ");
		sql2.append(" select bb.*,b1.abbr_name custName,b1.name chName,b1.full_name enName,h.jobManager,d.finalAr,if(p1.id > 0,'有','无') isPn ,c1.percentSum  from ( select  a.id,a.id offerId,a2.cust_id custId,a2.name jobName,a.recom_id recomId,b.cv_id cvId,c.cv_name cvName,c.mobile,c.email,b.job_id jobId,a.apply_d applyD,a.onduty_d ondutyD,a.trial,a.expired_d expiredD,a.salary,a.currency,a.estimate_ar estimateAr,a.checkin_d checkinD,a.status,a.status_desc statusDesc,b.status cvJobStatus,a.adjust_ar adjustAr,a.adjust_sign adjustSign,a.adjust_desc adjustDesc,a.pn_status pnStatus,a.creator,a.create_dt createDt,a.modifier,a.modified_dt modifiedDt from contract_job_cv_offer a,contract_job_cv_recom b,contract_job a2,cv c where a.recom_id=b.id and b.cv_id=c.id and b.job_id=a2.id and c.is_delete=0 ) bb ");
		sql2.append(" left join  (select t2.id,t1.name,t1.full_name,t1.abbr_name from company t1,customer t2 where t1.id=t2.company_id ) b1 on bb.custId=b1.id ");
		sql2.append(" left join  ( select a.job_id,group_concat((b.account) separator ',') as jobManager from contract_job_partner a,system_user b where a.account=b.account and a.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' group by a.job_id) h on h.job_id=bb.jobId");
		sql2.append(" left join  (select b.offer_id,sum(ifnull(a.ar_amount,0)) finalAr,b.id  from account_ar a,account_pn b where a.ar_dt is not null and a.pn_nbr=b.nbr group by b.offer_id) d on d.offer_id=bb.id ");
		// sql2.append(" left join  ( select a.job_id,group_concat((b.name) separator ',') as jobManager from contract_job_partner a,system_user b where a.account=b.account and a.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' group by a.job_id) h on h.job_id=a.id");
		sql2.append(" left join  account_pn p1 on bb.offerId = p1.offer_id ");
		// 业绩分配 MC-职位负责人 PC-合作顾问
		sql2.append(" left join (select a.offer_id,sum(a.percent) percentSum from contract_job_offer_partner a where 1=1 and a.role in ('PC','MC') group by a.offer_id ) c1 on c1.offer_id=bb.offerId ");
		sql2.append(" ) t where 1=1 ");

		if (isNotEmpty(query.getId())) {
			sql2.append(" and  t.id = ? ");
			values.add(query.getId());
		}
		if (isNotEmpty(query.getJobId())) {
			sql2.append(" and  t.jobId = ? ");
			values.add(query.getJobId());
		}
		if (isNotEmpty(query.getCvId())) {
			sql2.append(" and  t.cvId = ? ");
			values.add(query.getCvId());
		}
		if (isNotEmpty(query.getRecomId())) {
			sql2.append(" and  t.recomId = ? ");
			values.add(query.getRecomId());
		}
		if (isNotEmpty(query.getJobName())) {
			sql2.append(" and t.jobName like ? ");
			values.add("%" + query.getJobName() + "%");
		}
		if (isNotEmpty(query.getCustName())) {
			sql2.append(" and ( t.custName like ?  or chName like ? or enName like ? )");
			values.add("%" + query.getCustName() + "%");
			values.add("%" + query.getCustName() + "%");
			values.add("%" + query.getCustName() + "%");
		}
		if (isNotEmpty(query.getCvName())) {
			sql2.append(" and t.cvName like ? ");
			values.add("%" + query.getCvName() + "%");
		}
		if (isNotEmpty(query.getJobManager())) {
			sql2.append(" and t.jobManager like ? ");
			values.add("%" + query.getJobManager() + "%");
		}
		if (isNotEmpty(query.getIsPn())) {
			sql2.append(" and  t.isPn = ? ");
			values.add(query.getIsPn());
		}
		if (isNotEmpty(query.getPercentStatus())) {
			if (query.getPercentStatus().equals("N")) { // normal 正常
				sql2.append(" and  t.percentSum = 100 ");
			}
			if (query.getPercentStatus().equals("E")) { // Exception 异常
				sql2.append(" and  ifnull(t.percentSum,0) != 100  ");
			}
		}
		// 增加权限控制

		if (query.getOnlineUser() != null) {
			String authsql = AuthSqlUtil.authJobSQL(query.getOnlineUser().getUsername(), "t.jobId", "t.creator");
			if (!StringHelper.isEmpty(authsql))
				sql2.append(authsql);
			System.out.println("add authsql:" + authsql);
		}
		if (isNotEmpty(query.getSort())) {
			sql2.append(" order by t.").append(query.getSort());

			if (isNotEmpty(query.getDir())) {
				sql2.append(" ").append(query.getDir());
			}
		}
		// sql2.append(" order by t.id desc ");

		return super.getJdbcDao().createPage(sql2.toString(), values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}

	@Override
	public List exportCvOfferList(ContractJobCvOfferQuery query) {
		// TODO Auto-generated method stub
		// 职位，人选，录用时间(apply_d)，上班时间(a.onduty_d)，录用结果，预计收费estimate_ar，实际收费final_ar，保证期到期日？
		// 录用公司 客户职位联系人，联系人电话，联系人电邮
		// XsqlBuilder syntax,please see
		// http://code.google.com/p/rapid-xsqlbuilder
		// [column]为字符串拼接, {column}为使用占位符.
		// [column]为使用字符串拼接,如username='[username]',偷懒时可以使用字符串拼接
		// [column] 为PageRequest的属性
		Class[] os = { ContractJobCvOfferQuery.class };
		Class property = org.springframework.beans.BeanUtils.findPropertyType(query.getField_type(), os);
		if (property != null) {
			if ("java.lang.String".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), query.getQuery());

			} else if ("java.lang.Integer".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseInteger(query.getQuery(), 0));

			} else if ("java.lang.Double".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseDouble(query.getQuery(), 0));
			} else if ("java.util.Date".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), DateHelper.toDate(query.getQuery(), DateHelper.DEFAULT_DATE_FORMAT));
			}
		}
		// 生成sql2的原因是为了不喜欢使用xsqlbuilder的同学，请修改生成器模板，删除本段的生成 ///顺序为：0 人选接受offer，1
		// 已经上班，3保证期离职，2人选拒绝，-1客户取消
		StringBuffer sql2 = new StringBuffer();
		List values = new ArrayList();
		sql2.append("select * from ( ");
		sql2.append(" select bb.*,b1.name custName,h.jobManager,d.finalAr,if(p1.id > 0,'有','无') isPn  from ( select  a.id,a.id offerId,a2.cust_id custId,a2.name jobName,a.recom_id recomId,b.cv_id cvId,c.cv_name cvName,c.mobile,c.email,b.job_id jobId,a.apply_d applyD,a.onduty_d ondutyD,a.trial,a.expired_d expiredD,a.salary,a.currency,a.estimate_ar estimateAr,a.checkin_d checkinD,a.status_desc statusDesc,b.status cvJobStatus,a.adjust_ar adjustAr,a.adjust_sign adjustSign,a.adjust_desc adjustDesc,a.pn_status pnStatus,a.creator,a.create_dt createDt,a.modifier,a.modified_dt modifiedDt ");
		sql2.append(" ,case when a.status = 0 then '人选接受offer' ");
		sql2.append(" when a.status = 1 then '已经上班' ");
		sql2.append(" when a.status = 2 then '人选拒绝' ");
		sql2.append(" when a.status = 3 then '保证期离职' ");
		sql2.append(" when a.status =-1 then '客户取消' ");
		sql2.append(" end status ");
		sql2.append(" from contract_job_cv_offer a,contract_job_cv_recom b,contract_job a2,cv c where a.recom_id=b.id and b.cv_id=c.id and b.job_id=a2.id and c.is_delete=0 ) bb ");
		sql2.append(" left join  (select t2.id,t1.name,t1.full_name from company t1,customer t2 where t1.id=t2.company_id ) b1 on bb.custId=b1.id ");
		sql2.append(" left join  ( select a.job_id,group_concat((b.name) separator ',') as jobManager from contract_job_partner a,system_user b where a.account=b.account and a.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' group by a.job_id) h on h.job_id=bb.jobId");
		sql2.append(" left join  (select b.offer_id,sum(ifnull(a.ar_amount,0)) finalAr,b.id  from account_ar a,account_pn b where a.ar_dt is not null and a.pn_nbr=b.nbr group by b.offer_id) d on d.offer_id=bb.id ");
		// sql2.append(" left join  ( select a.job_id,group_concat((b.name) separator ',') as jobManager from contract_job_partner a,system_user b where a.account=b.account and a.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' group by a.job_id) h on h.job_id=a.id");
		sql2.append(" left join  account_pn p1 on bb.offerId = p1.offer_id ");
		sql2.append(" ) t where 1=1 ");

		if (isNotEmpty(query.getId())) {
			sql2.append(" and  t.id = ? ");
			values.add(query.getId());
		}
		if (isNotEmpty(query.getJobId())) {
			sql2.append(" and  t.jobId = ? ");
			values.add(query.getJobId());
		}
		if (isNotEmpty(query.getCvId())) {
			sql2.append(" and  t.cvId = ? ");
			values.add(query.getCvId());
		}
		if (isNotEmpty(query.getRecomId())) {
			sql2.append(" and  t.recomId = ? ");
			values.add(query.getRecomId());
		}
		if (isNotEmpty(query.getJobName())) {
			sql2.append(" and t.jobName like ? ");
			values.add("%" + query.getJobName() + "%");
		}
		if (isNotEmpty(query.getCustName())) {
			sql2.append(" and t.custName like ? ");
			values.add("%" + query.getCustName() + "%");
		}
		if (isNotEmpty(query.getCvName())) {
			sql2.append(" and t.cvName like ? ");
			values.add("%" + query.getCvName() + "%");
		}
		if (isNotEmpty(query.getJobManager())) {
			sql2.append(" and t.jobManager like ? ");
			values.add("%" + query.getJobManager() + "%");
		}
		if (isNotEmpty(query.getIsPn())) {
			sql2.append(" and  t.isPn = ? ");
			values.add(query.getIsPn());
		}
		// 增加权限控制

		if (query.getOnlineUser() != null) {
			String authsql = AuthSqlUtil.authJobSQL(query.getOnlineUser().getUsername(), "t.jobId", "t.creator");
			if (!StringHelper.isEmpty(authsql))
				sql2.append(authsql);
			System.out.println("add authsql:" + authsql);
		}
		if (isNotEmpty(query.getSort())) {
			sql2.append(" order by t.").append(query.getSort());

			if (isNotEmpty(query.getDir())) {
				sql2.append(" ").append(query.getDir());
			}
		}
		// sql2.append(" order by t.id desc ");

		return super.getJdbcDao().findByQuery(sql2.toString(), values.toArray(), new MapObjectRowMapper());
	}

}
